import 'dotenv/config';
import fs from 'fs';
import path from 'path';
import { fileURLToPath } from 'url';
import { pool } from '../src/db/mysql.js';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

// 解析制表符 TSV，支持双引号包裹与字段内换行
function parseTSVWithQuotes(text) {
  const rows = [];
  let row = [];
  let field = '';
  let inQuotes = false;

  const pushField = () => {
    row.push(field);
    field = '';
  };
  const pushRow = () => {
    rows.push(row);
    row = [];
  };

  for (let i = 0; i < text.length; i++) {
    const ch = text[i];

    if (inQuotes) {
      if (ch === '"') {
        const next = text[i + 1];
        if (next === '"') {
          // 转义的双引号 "" -> "
          field += '"';
          i++;
        } else {
          inQuotes = false;
        }
      } else {
        field += ch;
      }
    } else {
      if (ch === '"') {
        inQuotes = true;
      } else if (ch === '\t') {
        pushField();
      } else if (ch === '\r') {
        // ignore, wait for \n
      } else if (ch === '\n') {
        pushField();
        pushRow();
      } else {
        field += ch;
      }
    }
  }
  // 最后一行（无换行结尾）
  if (field.length > 0 || row.length > 0) {
    pushField();
    pushRow();
  }
  return rows;
}

function secToDuration(sec) {
  const n = Number(sec);
  if (!Number.isFinite(n) || n <= 0) return null;
  const m = Math.floor(n / 60);
  const s = Math.floor(n % 60);
  return `${m}:${String(s).padStart(2, '0')}`;
}

async function main() {
  const args = process.argv.slice(2);
  let txtPath = 'data/songs.txt';
  let truncate = false;

  for (const a of args) {
    if (a === '--truncate') truncate = true;
    else txtPath = a;
  }

  const resolved = path.isAbsolute(txtPath) ? txtPath : path.resolve(path.join(__dirname, '..'), txtPath);
  if (!fs.existsSync(resolved)) {
    console.error(`File not found: ${resolved}`);
    process.exit(2);
  }
  const raw = fs.readFileSync(resolved, 'utf-8');
  const rows = parseTSVWithQuotes(raw).filter(r => r.length > 0);

  if (rows.length === 0) {
    console.log('No data rows found.');
    return;
  }

  // 列期望（按你的样例）： 
  // 0:id 1:title 2:artist 3:album 4:cover 5:url 6:durationSeconds 7:... 8:... 9:createdAt 10:updatedAt 11:lyrics
  // 其余列忽略
  const conn = await pool.getConnection();
  try {
    await conn.beginTransaction();

    if (truncate) {
      await conn.query('TRUNCATE TABLE songs');
      console.log('Truncated table songs');
    }

    let inserted = 0, updated = 0, skipped = 0;
    for (const r of rows) {
      // 跳过空行
      const cols = r.map(v => (v === '' ? null : v));
      const idRaw = cols[0];
      const title = cols[1];
      const artist = cols[2];
      const album = cols[3];
      const cover = cols[4];
      const url = cols[5];
      const duration = secToDuration(cols[6]);
      const lyrics = cols[11]; // 允许多行，已在解析器保留

      if (!title || !artist) {
        skipped++;
        continue;
      }

      const idNum = idRaw != null && idRaw !== '' ? Number(idRaw) : null;

      if (idNum && idNum > 0) {
        const sql = `
          INSERT INTO songs (id, title, artist, album, cover, duration, url, lyrics)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)
          ON DUPLICATE KEY UPDATE
            title = VALUES(title),
            artist = VALUES(artist),
            album = VALUES(album),
            cover = VALUES(cover),
            duration = VALUES(duration),
            url = VALUES(url),
            lyrics = VALUES(lyrics)
        `;
        const [res] = await conn.query(sql, [idNum, title, artist, album, cover, duration, url, lyrics]);
        if (res.affectedRows === 1) inserted++;
        else if (res.affectedRows === 2) updated++;
      } else {
        const sql = `
          INSERT INTO songs (title, artist, album, cover, duration, url, lyrics)
          VALUES (?, ?, ?, ?, ?, ?, ?)
        `;
        await conn.query(sql, [title, artist, album, cover, duration, url, lyrics]);
        inserted++;
      }
    }

    await conn.commit();
    console.log(`Done. inserted=${inserted}, updated=${updated}, skipped=${skipped}`);
  } catch (e) {
    await conn.rollback();
    console.error('Failed. Transaction rolled back.', e);
    process.exit(5);
  } finally {
    conn.release();
    await pool.end();
  }
}

main().catch(e => {
  console.error(e);
  process.exit(10);
});